﻿Public Class EstadoDAO

    Dim objAcessoDados As AcessoDados = New AcessoDados(Config.StringConexao)

    Public Function ListaTodos() As List(Of Estado)
        Dim registros As DataSet = New DataSet
        registros = objAcessoDados.ExecutaSqlRetorno("select * from tb_estado")
        Return CriarListaEst(registros)
    End Function

    Public Function CriarListaEst(ByVal Registros As DataSet) As List(Of Estado)
        Dim lista As New List(Of Estado)
        Dim objEst As Estado
        For Each dr As DataRow In Registros.Tables(0).Rows
            objEst = CriarEst(dr)
            lista.Add(objEst)
        Next
        Return lista
    End Function
    Public Function CriarEst(ByVal row As DataRow) As Estado
        Dim objEst As New Estado

        objEst.CodEst = CInt(row.Item("cod_est"))
        objEst.NomeEst = CStr(row.Item("nome_est"))
     

        Return objEst
    End Function

    Public Function Inserir(ByVal iEstado As Estado) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder

        sql.Append("insert into tb_estado (")
        sql.Append(" nome_est ")
        sql.Append(") values (")
        sql.Append("'" & iEstado.NomeEst.ToString & "')")
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function SelectEstadoId(ByVal cod_est As Integer) As Estado
        Dim objEst As New Estado
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_estado ")
        sql.Append(strWhere & "cod_est = " & cod_est)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objEstData As DataRow = registro.Tables(0).Rows(0)
            objEst = CriarEst(objEstData)
            Return objEst
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function SelectEstadoNome(ByVal est As Estado) As Estado
        Dim objEst As New Estado
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_estado ")
        sql.Append(strWhere & "nome_est = '" & est.NomeEst.ToString & "'")
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objEstData As DataRow = registro.Tables(0).Rows(0)
            objEst = CriarEst(objEstData)
            Return objEst
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

End Class
